Arrays
Select Entire Array
Enter a specific array name instead of * with select command to query the entire array by it's name. In case you query a not existing array, you get NULL in the response.
Template: select array_name from stream_name
#returns all Entires arrays from binance2 stream
SELECT entries FROM binance2
Select Array Elements
Use dot separator to specify a specific array element.
Template: select array_name.field_name from stream_name
#select all Entries arrays
SELECT entries FROM bitfinex
#return an array of Price fields from all Entries arrays
SELECT entries.price FROM bitfinex
#check all array Price elements against the specified condition and return an array of boolean elements
SELECT entries.price > 10 FROM bitfinex
#checks array values and returns a single true/false per array
SELECT ANY(entries.price > 10) FROM bitfinex
#use alias name as object reference
SELECT
(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry] AS trades).price AS 'Price',
trades.size AS 'Size'
FROM kraken
WHERE size(trades) > 0
#returns arrays of Price values that match the condition
SELECT entries.price AS 'price' FROM bitfinex
WHERE ANY(entries.price > 10)
#selecting Bid and Ask prices and sizes from Entries array in the L1entry object
WITH
entries AS array(deltix.timebase.api.messages.universal.L1entry) AS l1
SELECT
l1[side == ASK].price AS 'askPrice',
l1[side == ASK].size AS 'askSize',
l1[side == BID].price AS 'bidPrice',
l1[side == BID].size AS 'bidSize'
FROM binance
WHERE size(l1) > 0
#select all Prices from Entries array in the L1Entry object that are both > 0 and > 20
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price AS 'Price' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0 AND ALL(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price > 20)
Select Embedded Arrays
QQL allows selecting arrays within arrays. Such queries return arrays with elements from embedded arrays.
Template: select array_name.embedded_array_name.field_name from stream_name
#returns all Attributes arrays that are embedded in the Entries array
SELECT entries.attributes FROM packages
#returns an array with all Value fields from all Attributes arrays that are embedded in the Entries array
SELECT entries.attributes.value FROM packages
Filter Arrays
Filter by Type
Use keyword THIS to filter arrays by type.
#select Entries arrays from L1Entry object type that meet the condition
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry] AS 'entry' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0
#select specific fields from Entries array within the L1Entry object
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price AS 'Price', entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].size AS 'Size' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0
Nullability Check
SELECT entries AS array(deltix.entries.TradeEntry)[THIS IS NOT NULL] FROM packages
Predicates
You can use THIS variable to iterate through array elements, filter objects by type, filter object fields against a specified condition.
#returns just Entries array elements of a Trade type. In this case we check all Entries array elements one by one and select just the ones of a Trade type.
SELECT entries[THIS IS Trade] FROM stream
#In this case we check all Size values in all Entries array elements and return only those that meet the query condition (>10). Query returns an arrays of Size values that are greater than 10.
SELECT entries.size[THIS > 10] FROM packages
SELECT entries[THIS.size > 10].size FROM packages
SELECT entries[size > 10].size FROM packages
#returns all Entries array elements where Price field value is greater than 2000
SELECT entries[THIS.price > 2000] FROM packages
#same as previous - THIS variable can be skipped in this case
SELECT entries[price > 2000] FROM packages
#take Entry objects where Price is greater than 4000 and select Price fields that are less than 5000
SELECT entry[price > 4000].price[THIS < 5000] FROM packages
ARRAY JOIN entries AS entry
Select by Element Index
You can select array elements by their indices.
Template: select array_name[array_element_index] from stream_name
#select Entries array with index = 10
SELECT entries[10] FROM packages
#select symbol and max Price with index = 0 from Entries array from binance2 stream and aggregate all by symbol
SELECT symbol, max(entries.price [0]) FROM binance2
GROUP BY symbol
Selecting array records with negative indices.
Template: select array_name[array_element_index] from stream_name
#returns records in the reversed order. This query returns the first array record from the tail.
SELECT entries[-1] FROM packages
Selecting an array of records by their indices.
Template: select array_name[[index_array]] from stream_name
#returns an array of Entries records according to their indices
SELECT entries[[2, 3]] FROM packages
Selecting array elements using an array of Boolean values.
Each Boolean element corresponds to the array element index.
Template: select array_name[[boolean_values_array]] from stream_name
#returns array elements with indices 0 and 2
SELECT entries[[True, False, True]] FROM packages
#returns all records with price greater than 2000. [entries.price > 2000] returns an array of boolean values.
#See Comparison Operations further in this manual.
SELECT entries[entries.price > 2000] FROM packages
Slicing Queries
Slicing queries use index ranges to query array elements.
Template: select array_name[index_range] from stream_name
#select all Entries array elements between indices 1 and 2
SELECT entries[1:2] FROM packages
#select all Entries array elements with index less than 2
SELECT entries[:2] FROM packages
#select all Entries array elements with index greater than 2
SELECT entries[2:] FROM packages
Slicing with Steps
Template: select array_name[index_range_with_step] from stream_name
#select all Entries array elements
SELECT entries[::] FROM packages
#select all Entries array elements in the reversed order
SELECT entries[::-1] FROM packages
#select Entries array elements with indices from 1 to 5 with step=2
SELECT entries[1:5:2] FROM packages
Position
Position function can be applied only within square brackets [] and allows checking each array element one by one by their indices against a specified condition.
#select Price from Entries array with indices that are greater than 3
SELECT entries[position() > 3].price FROM stream
#select Price from Entries array with both index that is greater than 3 and Price value that is less than 1000
SELECT entries[price < 1000 and position() > 3].price FROM stream
Array Join
Array Join function allows executing Join operation on nested arrays.
Array Join unfolds nested arrays and joins every array element with the existing stream message. Empty elements are skipped.
Example for illustration purposes:
Consider the following stream:
| timeStamp | numbers | characters |
|---|---|---|
| 1, | [1,2,3], | [] |
| 2, | [3,4,5], | [] |
| 3, | [], | [a,b,c] |
| 4, | [], | [e,f,g] |
Let's make Array Join by Characters:
#Numbers arrays are NULL, because we Join by Characters arrays
SELECT * FROM stream
ARRAY JOIN characterstimeStamp numbers characters 3, NULL, a 3, NULL, b 3, NULL, c 4, NULL, e 4, NULL, f 4, NULL, g We can get the same result a bit different way:
SELECT timestamp, numbers, characters FROM stream
ARRAY JOIN characterstimeStamp numbers characters 3, NULL, a 3, NULL, b 3, NULL, c 4, NULL, e 4, NULL, f 4, NULL, g Let's make Array Join by Numbers:
#Characters arrays are NULL, because we Join by Numbers arrays
SELECT timestamp, numbers, characters FROM stream
ARRAY JOIN numberstimeStamp numbers characters 1, 1, NULL 1, 2, NULL 1, 3, NULL 2, 3, NULL 2, 4, NULL 2, 5, NULL Now, let's perform Array Join by both types of arrays:
SELECT timestamp, numbers, characters FROM stream
ARRAY JOIN numbers, characterstimeStamp numbers characters 1, 1, NULL 1, 2, NULL 1, 3, NULL 2, 3, NULL 2, 4, NULL 2, 5, NULL 3, NULL, a 3, NULL, b 3, NULL, c 4, NULL, e 4, NULL, f 4, NULL, g
Close to reality example 1:
#In this example we filtered Entries array by type to output just L2EntryNew type.
#We use Array Join to unfold Entries array and create a new field called Entry, where each element is Entries array element.
SELECT entry FROM binance2
ARRAY JOIN entries[THIS IS deltix.timebase.api.messages.universal.L2EntryNew] AS entry
Close to reality example 2:
#select Price and Size from Entries array from KrakenTradeEntry message type
#cast Entries array to KrakenTradeEntry type
#make array join by Price and Size
SELECT entry.price AS 'TradePrice', entry.size AS 'TradeSize'
FROM kraken
ARRAY JOIN (entries AS array(deltix.qsrv.hf.plugins.data.kraken.types.KrakenTradeEntry))[THIS IS NOT NULL] AS entry
Close to reality example 3:
#select BID Price/Size and ASK Price/Size from Entry object
#cast to L1entry object type
#array join to output BID Price/Size and ASK Price/Size as individual fields
SELECT
entry[side == ASK].price AS askPrice,
entry[side == ASK].size AS askSize,
entry[side == BID].price AS bidPrice,
entry[side == BID].size AS bidSize
FROM kraken
ARRAY JOIN (entries as array(deltix.timebase.api.messages.universal.L1entry))[THIS IS NOT NULL] AS entry
Using Array Join with Asterisk
You can use * in combination with aliases to make Array Join as a new field. In such cases a new fields with Join result is added to the existing set of fields.
#New field Char is added with Characters array elements as a result of a Join
SELECT * FROM stream
ARRAY JOIN characters AS char
| timeStamp | numbers | characters | char |
|---|---|---|---|
| 3, | NULL, | [a,b,c], | a |
| 3, | NULL, | [a,b,c], | b |
| 3, | NULL, | [a,b,c], | c |
| 4, | NULL, | [e,f,g], | e |
| 4, | NULL, | [e,f,g], | f |
| 4, | NULL, | [e,f,g], | g |
#We Join Characters as Numbers and as a result Numbers type is changed to string.
SELECT * FROM stream
ARRAY JOIN characters AS numbers
| timeStamp | numbers | characters |
|---|---|---|
| 3, | a, | [a,b,c] |
| 3, | b, | [a,b,c] |
| 3, | c, | [a,b,c] |
| 4, | e, | [e,f,g] |
| 4, | f, | [e,f,g] |
| 4, | g, | [e,f,g] |
Close to reality example:
In this example we unfold Entries array and create a new field called Joined_Field, where each entry is Entries array element.
Operations with Array Join
You can apply other, mentioned in this tutorial, operations in combinations with Array Joins.
#select and Joins just Price > 2000 as Entry from the Entries array
SELECT price FROM stream
ARRAY JOIN entries AS entry
WHERE entry.price > 2000
#select and Join Price as TradePrice just from L1Entry array type
SELECT price FROM stream
ARRAY JOIN entries.price AS TradePrice
WHERE entry IS L1Entry
#Join with selected fields from nested arrays. In this example we select just the Value field from the Attributes array and Join it as Attribute with the remaining message fields.
SELECT attributes.value FROM stream
ARRAY JOIN entries.attributes AS attribute
Join Multiple Arrays
You can use Array Join with more than one type of array.
#use Array Join to unfold Entries polymorphic array and output various array types as separate fields
SELECT entry, num FROM packages
ARRAY JOIN entries AS entry, enumerate(entries) AS num
Left Array Join
You can use Left Array Join to display elements that are not joined. In this case, not joined elements are returned as is. Joined elements are joined the regular way.
Example for illustration purposes:
Let's consider the following stream.
| timeStamp | numbers | characters |
|---|---|---|
| 1, | [1,2,3], | [] |
| 2, | [3,4,5], | [] |
| 3, | [], | [a,b,c] |
| 4, | [], | [e,f,g] |
Let's make Left Join by Characters. In this case Numbers are returned without changes and Characters are unfolded into 6 messages.
#Numbers array remain as is. Join is made by Character array elements.
SELECT timeStamp, numbers, characters FROM stream
LEFT ARRAY JOIN characters
| timeStamp | numbers | characters |
|---|---|---|
| 1, | [1,2,3], | [] |
| 2, | [3,4,5], | [] |
| 3, | NULL, | a |
| 3, | NULL, | b |
| 3, | NULL, | c |
| 4, | NULL, | e |
| 4, | NULL, | f |
| 4, | NULL, | g |